<?
/******************************************************************************
 Report page
 created by Sivakumaran
 File Description:
 		To Show report for received report_id.
	 	
/*****************************************************************************/
include("../../includes/global_files/tables.inc");
include(DOCINCLUDES."global_files/global_locale.inc");
include(DOCINCLUDES."global_files/global_html.inc");
include(DOCINCLUDES."global_files/report_php.inc");
include(DOCINCLUDES."global_files/tables.inc");
include(DOCINCLUDES."global_files/global_session.inc");
include(DOCINCLUDES."global_files/form_php.inc");
include(DOCINCLUDES."global_files/global_pgsql.inc");




$db = new cDatabase();
if ($_GET["action"]=="excel_report")
{
include(DOCINCLUDES."global_files/global_spreadsheet.inc");
$spsheet= new cSpreadsheet();
$report_id=$_GET["report_id"];
$date=date("ymd");
$master_report="master_report";

$db->query="Select report_name from $master_report where report_id=$report_id";
	$db->runQuery();
$report_array=$db->returnArrays();
$spreadsheet_name=str_replace(" ","_",$report_array[0]['report_name']);

$spsheet->createSpreadsheet($_SESSION['excel'],$date."_".$spreadsheet_name,"Sheet1");
exit();
}

include(DOCINCLUDES."global_files/global_filter_functions.inc");
$page = new cPage();
$form = new cForm();

$page->header_path="../../";

if($_GET)
        {
	$report_id	=$_GET["report_id"];
	$report_status	=$_GET["action"];
	if($report_id)
	{
	$db->query="select customization_id from $sys_report_customization_table where is_active='t' and report_id=$report_id;";
print_r($db_cus_id);exit;	
        $db->runQuery();
	
        $db_cus_id=$db->returnArrays();
	
        $customization_id=$_GET["customization_id"]?$_GET["customization_id"]:$db_cus_id[0]["customization_id"];
        
	}
	else
	$customization_id=$_GET["customization_id"];
        $from_date	=$_GET["from_date"];
        $to_date	=$_GET["to_date"];
        $group_name	=$_GET["group_name"];
        $group_order	=$_GET["group_order"];
        $sort_name	=$_GET["sort_name"];
        $sort_order	=$_GET["sort_order"];
	$date_type	=$_GET["datecondition"];
        }
        
if($_POST)
        {
	$report_id	=$_POST["report_id"];
	}	

if($_POST)
        {
	$customization_id=$_POST["customizations"]?$_POST["customizations"]:$_POST["customization_id"];
	unset($_SESSION["cache"]["data"]);
	}

        $report_customization	= getReportDetails($company_id,$customization_id);
$db->query="select customization_name from $sys_report_customization_table where customization_id=$customization_id";
$db->runQuery();
$cus_name_array=$db->returnArrays();
$customization_name=$cus_name_array[0]["customization_name"];

if($_POST)
        {
        $form->validations["from_date"]=array("isEmpty"=>"Please enter from date","isDate"=>"Please enter a valid date");
        $form->validations["to_date"]=array("isEmpty"=>"Please enter to date","isDate"=>"Please enter a valid date");
       // $form->validate($_POST);

	$from_date	= $_POST["from_date"];
        $to_date	= $_POST["to_date"];
	$from_no	= $_POST["from_no"];
        $to_no		= $_POST["to_no"];
	$report_id	= $_POST["report_id"];
	$report_status	= $_POST["action"];
	$_POST["selected_columns"]=substr($_POST["selected_columns"],1);
        $group_id_array	= explode(",",$_POST["selected_columns"]);
        $group_order	= $_POST["group_order"];
        $sort_name	= $_POST["sort_name"];
        $sort_order	= $_POST["sort_order"];
	$date_type 	= $_POST["datecondition"];
	$number_condition= $_POST["numbercondition"];

	if($date_type)
		{
		$date_type_condition =" $date_type ";
		}
	if($number_condition)
		{
		$number_type_condition =" $number_condition ";
		}
	if($from_date)
		{
		$formatted_from_date=getFormattedDate($from_date);
		}
	if($to_date)
		{
		$formatted_to_date=getFormattedDate($to_date);
		}

	}
	$db->query="select

				srf.filter_column,
				src.filter_id,
				smf.filter_name,
				smf.form_display,
				src.default_value

				from
				$sys_master_filter_table smf
				join $sys_report_filter_table srf
				on smf.filter_id=srf.filter_id
				join $sys_filter_customization_table src
				on srf.filter_id=src.filter_id

				where src.customization_id=$customization_id";
	$db->runQuery();
	$db_filter_names_array=$db->returnArrays();

	for($i=0,$j=0;$i<count($db_filter_names_array);$i++)
	{
		$db->query="select

				script_variable_name
				from
				$master_column_mapping_table
				where column_id=".$db_filter_names_array[$i][filter_column].";";
		$db->runQuery();
		$db_script_name_array=$db->returnArrays();
	if($_POST["show"])
		{
		if(($_POST[$db_filter_names_array[$i][filter_name]][0])!="" && $j==0)
			{
			$and_where_string=" where ";
			$j=1;
			}
		elseif(($_POST[$db_filter_names_array[$i][filter_name]][0])!="" && $j==1)
			{	
			$and_where_string=" and ";
			}
		//for each condition available writing the db query string
		$constraint_condition.=(($_POST[$db_filter_names_array[$i][filter_name]][0])!="")?"".$and_where_string." ".$db_script_name_array[0]["script_variable_name"]." in (".implode(',',addQuotes($_POST[$db_filter_names_array[$i][filter_name]])).")":"";
		if($_POST["save_setting"])
			{
			$filter_id=$db_filter_names_array[$i]["filter_id"];
			$default_value=(($_POST[$db_filter_names_array[$i][filter_name]][0])!="")?implode('}',$_POST[$db_filter_names_array[$i][filter_name]]):"";
			$db->query="update
			$sys_filter_customization_table
			set

			default_value='".$default_value."'

			where
			customization_id=$customization_id and
			filter_id=$filter_id";
			$db->runQuery();
			}

		}
	if($constraint_condition=="")
		{	
		if(substr($db_filter_names_array[$i]["default_value"],-1)=="}");
			$db_filter_names_array[$i]["default_value"]=substr($db_filter_names_array[$i]["default_value"],0,-1);
		
		if(($db_filter_names_array[$i][default_value])!="" && $j==0)
			{
			$and_where_string=" where ";
			$j=1;
			}
		elseif(($db_filter_names_array[$i][default_value])!="" && $j==1)
			{	
			$and_where_string=" and ";
			}
		
		$default_condition.=(($db_filter_names_array[$i][default_value])!="")?"".$and_where_string."".$db_script_name_array[0]["script_variable_name"]." in (".implode(',',addQuotes(explode('}',$db_filter_names_array[$i][default_value]))).")":"";
		
		if($db_filter_names_array[$i][default_value])
		$applied_condition_array[0][$db_filter_names_array[$i][form_display]]=implode(',',(explode('}',$db_filter_names_array[$i][default_value])));
		}

		unset($db_script_name_array);
	}
	if($group_id_array[0]!="")
	{
		//$column_string=$_POST["selected_columns"];
		for($i=0;$i<count($group_id_array);$i++)
		{
		$db->query="select script_variable_name from $master_column_mapping_table where column_id =$group_id_array[$i]";
		$db->runQuery();
		$group_db_array=$db->returnArrays();
		$group_name[$i]=$group_db_array[0]["script_variable_name"];
		unset($group_db_array);
		if($_POST["save_setting"])
			{
			$db->query="update $report_customization_table set is_group='f' where customization_id=$customization_id";
			$db->runQuery();

			$db->query="update $report_customization_table set is_group='t' where customization_id=$customization_id and column_id=$group_id_array[$i]";
			$db->runQuery();
			}
		}
	}
	else
		{
		$column_string="select column_id from $report_customization_table where is_group='t' and customization_id=$customization_id order by display_order";

		$db->query="select script_variable_name from $master_column_mapping_table where column_id in($column_string)";
		$db->runQuery();
		$group_db_array=$db->returnArrays();
		}
	if($group_db_array && !$group_name)
		{
		foreach($group_db_array as $key=>$value)
			{
			$group_name[$key]=$value["script_variable_name"];
			}
		}
	if($formatted_from_date && $formatted_to_date)
		{
		$range_condition="  $date_type_condition between '$formatted_from_date' and '$formatted_to_date'";
		$string_condition=" between ".$from_date." to ".$to_date."";
		}
	elseif($formatted_from_date)
		{
		$range_condition="  $date_type_condition >= '$formatted_from_date'";
		$string_condition=" greater than ".$from_date." ";
		}
	elseif($formatted_to_date)
		{
		$range_condition="  $date_type_condition <= '$formatted_to_date'";
		$string_condition=" less than ".$to_date." ";
		}
	if($from_no && $to_no)
		{
		$range_condition1="  $number_type_condition between $from_no and $to_no";
		$string_condition1=" between ".$from_no." to ".$to_no."";
		}
	elseif($from_no)
		{
		$range_condition1="  $number_type_condition >= $from_no";
		$string_condition1=" greater than ".$from_no." ";
		}
	elseif($to_no)
		{
		$range_condition1="  $number_type_condition <= $to_no";
		$string_condition1=" less than ".$to_no." ";
		}
	if($_POST && ($date_type_condition || $number_type_condition))
	{
	if($constraint_condition=="" && $default_condition=="" && $buyer_restriction_condition=="")
		$constraint_condition=" where ";
	else
		{
		if($date_type_condition || $number_type_condition)
			$constraint_condition.=" and ";
		}
	$constraint_condition.=($date_type_condition)?$range_condition:"";

	$constraint_condition.=($number_type_condition)?$range_condition1:"";
	}

$db->query="SELECT report_name from $sys_report_customization_table src join
$master_report_table mr on mr.report_id=src.report_id where
customization_id=$customization_id";	
	$db->runQuery();
$report_name_array=$db->returnArrays();

$page->title=$report_name_array[0]['report_name'];
if($report_status!="print_report")
	{
	$page->doHeaderHtml();
	$page->doCentreHtml();
	
	}
else
	$page->doPrintHeader();
	$db->query="select
				report_name,
				view_name

				from
				$master_report_table

				where report_id=$report_id";
	$db->runQuery();
	$report_details_array=$db->returnArrays();
	
	
        
	if($buyer_restriction_condition)
		{
		if($default_condition || $constraint_condition)
			{
			$buyer_restriction_condition=" and ".$buyer_restriction_condition;
			}
		else
			{
			$buyer_restriction_condition=" where ".$buyer_restriction_condition;
			}
		}
/*Fetch the list of orders*/
// 	if(!$_SESSION["cache"]["data"][$report_id])
// 		{
		$fetch_column	= getColumnNames($report_customization["column_mapping"]);

		$db->query="select

				$fetch_column
				from
			
$company_id.".$report_details_array[0]['view_name']."
				$default_condition $constraint_condition $buyer_restriction_condition";
// 				echo "<br><br>".$db->query;
		$db->runQuery();
		$arrays=$db->returnArrays();
		$_SESSION["cache"]["page"][$report_id]=$_SERVER["PHP_SELF"];
        	$_SESSION["cache"]["data"][$report_id]=$arrays;
		
// 		}
// 	else
//         	{
//         //assign value from the session and use the array to generate reports
//         	$arrays=$_SESSION["cache"]["data"][$report_id];
//         	}
	

if($report_status != "print_report" )
        {
        addNonPrintableColumns($arrays,$company_id);
	}
if($report_customization["summary_columns"]!="")
	{
	$grand_totals_array=getTotal($arrays);
	//setNA($grand_totals_array);
	assignPreferredSummary($grand_totals_array,$report_customization["display_summary_columns"]);

	assignPreferredColumns($grand_totals_array,$report_customization["display_columns"],$report_customization["decimal_places"],$report_customization["format_details"]);
	}
/*if($report_customization["summary_columns"]!="")
	{
	$grand_totals_array=getTotal($arrays);
	//setNA($grand_totals_array);
	assignPreferredSummary($grand_totals_array,$report_customization["display_summary_columns"]);
	assignPreferredColumns($grand_totals_array,$report_customization["display_columns"],$report_customization["decimal_places"],$report_customization["format_details"]);
	}*/
if($arrays);
	{
	$group=generateGroupArray($group_name,$group_order);
	$sort["$sort_name[0]"]=$sort_order[0];
	$pre_sort=($group)?key($group):key($sort);
	$pre_sort_order=($group)?current($group):current($sort);
	//pass the form values in query string
	$query_string=array("customization_id"=>$customization_id,"report_id"=>$report_id);
	$query_string=array_merge($query_string,generateGroupQueryString($group,$sort));
	$query_string_summary_report=array_merge($query_string,array("action"=>"summary_report"));

	$pre_sorted_arrays=sortArray($arrays,$pre_sort,$pre_sort_order);
	}
if($report_status != "print_report" )
{
$ROW_COUNT=400;
}
else
{
$ROW_COUNT=100;
}


if($report_status!="summary_report")
                {
                echo "<center> ".returnLink($_SERVER["PHP_SELF"],"Summary Report", $query_string_summary_report,"","","Click To View Summary Report")."</center>";
                }
if($report_status != "print_report")
		{
		include(DOCINCLUDES."report_menu.php");
		//echo "<br>";
		}
if(!$arrays)
     		{
		echo "<div align=\"center\">No Records on ".
$report_name_array[0]['report_name']."</div><br />";
		exit;
      		}
if($report_status=="summary_report" || $place_order)
        {
        $page_arrays=$pre_sorted_arrays;
        }
else
        {
	if(!$pre_sorted_arrays)
		$pre_sorted_arrays=array();
        $page_arrays=navigatePage($_GET["page_no"],$pre_sorted_arrays,$ROW_COUNT,$query_string);
        }

// showGroupedReport($page_arrays,$group,$sort,$report_status);

 if($report_customization["summary_columns"]==""||$arrays[0]=="")
{
showGroupedReport($page_arrays,$group,$sort,$_GET["report"],array(),true,true);
}
 else
{
showGroupedReport($page_arrays,$group,$sort,$_GET["report"],"",array(),false);
}

unset($_SESSION['excel']);
$_SESSION['excel']=$report_details;
doHorizontalTable($report_details,"100%","",$report_customization["column_style"
],true,true,false,"reporttable");
unset($_SESSION["report"]["pdf_report"],$_SESSION["report"]["justification"]);
$_SESSION["report"]["justification"]=$report_customization["column_style"];
$_SESSION["report"]["pdf_report"]=$report_details;


	doHorizontalTable($grand_totals_array,"80%","Grand Total",$report_customization["column_style"],true,true,false,"");
	$_SESSION["report"]["grand_total"]=$grand_totals_array;

if($report_status=="summary_report")
        {
        assignPreferredColumns($summary_details,$report_customization["display_columns"],$report_customization["decimal_places"],$report_customization["format_details"],"DEFAULT_ORDER",$report_customization["format_date"]);
        doHorizontalTable($summary_details,"90%","",$report_customization["column_style"],true,true,false,"");
        $query_string=$query_string_summary_report;
        }


?>
<script language="javascript" >

function closeWindow()
	{
	window.open('','_parent','');
	window.close();
	}
</script>